#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/9/21 10:03
# @Author : 闫峰
# @File    : ceshi1.py
import json
import threading
import time
from datetime import datetime

import pymongo

from utils.mysqlUtils.mongodb_utils import MongoDBLink
import os

from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.sql_server_utils import MSSQL
from concurrent.futures import ThreadPoolExecutor, wait, as_completed
from functools import partial

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class TestChange:
    mapping = {
        "packageLength": "Length",
        "packageWidth": "Width",
        "packageHeight": "Height"
    }

    def test_allocation_bill(self, AddTime_start, AddTime_end):
        erp_db = MSSQL(ensure_path_sep("\\config\\config.yaml"), "sqlserver_db_erp_base")
        mysql_db = Database(ensure_path_sep("\\testcases\\erp\\responsible_inventory\\config.yaml"),
                            "irobotbox_datacenter")
        insert_db = Database(ensure_path_sep("\\config\\ceshi_mysql_config.yaml"), "log_db")
        # 遍历RB_Inventory_DetailLog库，验证allocation_bill
        get_BillNumber = f"""
        SELECT TOP 5000 BillNumber,OriginBillNumber,WareHouseID,SKU,id,CustomerID,AddTime,BillType,OperateNum
        FROM [dbo].[RB_Inventory_DetailLog] 
        
        WHERE AddTime >= '{AddTime_start}' AND AddTime<='{AddTime_end}' 
        and id > %s order by id
        """

        id_offset = 0
        count = 0
        allocation_bill_data_out_res = []
        allocation_bill_data_in_res = []
        inventory_check_order_data_res = []
        other_order_data_res = []
        procurement_putin_data_res = []
        sales_putout_data_res = []
        supplier_returns_order_res = []
        get_warehouse_id = """
        SELECT id FROM [dbo].[rb_warehouse] WHERE warehousetype in (3,81)
        """
        warehouse_id_list = erp_db.ExecQuery(get_warehouse_id, ())
        get_warehouse_id = list(map(lambda x: x[0], warehouse_id_list))
        get_warehouse_id_81 = """
        SELECT id FROM [dbo].[rb_warehouse] WHERE warehousetype in (81)
        """
        warehouse_id_list_81 = erp_db.ExecQuery(get_warehouse_id_81, ())
        warehouse_id_list_81 = list(map(lambda x: x[0], warehouse_id_list_81))
        while True:
            res = erp_db.ExecQuery(get_BillNumber, (id_offset,))

            if res:
                id_offset = res[-1][4]
                # 过滤仓库类型是81的数据
                no_allocation_bill_res = list(filter(
                    lambda x: x[2] not in get_warehouse_id and x[5] == 1, res))
                res = list(filter(
                    lambda x: x[2] not in warehouse_id_list_81 and x[5] == 1, res))
                # 如果数据存在
                if no_allocation_bill_res:
                    inventory_check_order_data, other_order_data, procurement_putin_data, sales_putout_data, supplier_returns_order = self.handler_data_no_allocation_bill(
                        no_allocation_bill_res)
                    inventory_check_order_data_res += inventory_check_order_data
                    other_order_data_res += other_order_data
                    procurement_putin_data_res += procurement_putin_data
                    sales_putout_data_res += sales_putout_data
                    supplier_returns_order_res += supplier_returns_order
                    count += len(res)
                    # INFO.logger.info(f"thread_id={threading.current_thread().ident}  "
                    #                  f"AddTime_start={AddTime_start}   "
                    #                  f"AddTime_end={AddTime_end}   "
                    #                  f"验证数据量为{count}  ")
                if res:
                    allocation_bill_out_data, allocation_bill_in_data = self.handler_data(res)
                    allocation_bill_data_out_res += allocation_bill_out_data
                    allocation_bill_data_in_res += allocation_bill_in_data
                    count += len(res)
                INFO.logger.info(f"thread_id={threading.current_thread().ident}  "
                                 f"AddTime_start={AddTime_start}   "
                                 f"AddTime_end={AddTime_end}   "
                                 f"验证数据量为{count}  ")
            else:
                break

        if allocation_bill_data_out_res:
            sql = f"""               
            SELECT BillNumber,AllocationOrderNo,SKU,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.allocation_bill  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and BillType=5
            and WarehouseType not in (81)
            and ToWarehouseType not in (81)
            
            """
            actual_allocation_bill_data_out_res = mysql_db.fetch_all_tuple(sql, ())
            actual_allocation_bill_data_out_set = set(actual_allocation_bill_data_out_res)
            allocation_bill_data_set = set(allocation_bill_data_out_res)
            if allocation_bill_data_set == actual_allocation_bill_data_out_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" allocation_bill表缺失{len(allocation_bill_data_set - actual_allocation_bill_data_out_set)}  个 \n"
                                   f"缺失{allocation_bill_data_set - actual_allocation_bill_data_out_set}   \n"
                                   f"多了{actual_allocation_bill_data_out_set - allocation_bill_data_set}")
                # print(allocation_bill_data_set - actual_allocation_bill_data_set)
                # print(actual_allocation_bill_data_set - allocation_bill_data_set)
        if allocation_bill_data_in_res:
            sql = f"""                
            SELECT BillNumber,AllocationOrderNo,SKU ,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.allocation_bill  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and BillType=4
            and WarehouseType not in (81)
            and ToWarehouseType not in (81)

            """
            actual_allocation_bill_data_in_res = mysql_db.fetch_all_tuple(sql, ())
            actual_allocation_bill_data_in_set = set(actual_allocation_bill_data_in_res)
            allocation_bill_data_set = set(allocation_bill_data_in_res)
            if allocation_bill_data_set == actual_allocation_bill_data_in_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" allocation_bill表缺失{len(allocation_bill_data_set - actual_allocation_bill_data_in_set)}  个 \n"
                                   f"缺失{allocation_bill_data_set - actual_allocation_bill_data_in_set}   \n"
                                   f"多了{actual_allocation_bill_data_in_set - allocation_bill_data_set}")
                # print(allocation_bill_data_set - actual_allocation_bill_data_set)
                # print(actual_allocation_bill_data_set - allocation_bill_data_set)

        if inventory_check_order_data_res:
            sql = f"""               
            SELECT BillNumber,OrderNo,SKU,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.inventory_check_order  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and WarehouseType not in (3,81)
            """
            actual_inventory_check_order_data_res = mysql_db.fetch_all_tuple(sql, ())
            actual_inventory_check_order_data_set = set(actual_inventory_check_order_data_res)
            inventory_check_order_data_set = set(inventory_check_order_data_res)
            if inventory_check_order_data_set == actual_inventory_check_order_data_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" inventory_check_order 表缺失{len(inventory_check_order_data_set - actual_inventory_check_order_data_set)}  个 \n"
                                   f"缺失{inventory_check_order_data_set - actual_inventory_check_order_data_set}   \n"
                                   f"多了{actual_inventory_check_order_data_set - inventory_check_order_data_set}")
                # print(actual_inventory_check_order_data_set - inventory_check_order_data_set)
                # print(inventory_check_order_data_set - actual_inventory_check_order_data_set)

        if other_order_data_res:
            sql = f"""               
            SELECT BillNumber,OrderNo,SKU,ABS(ProductQuantity)
             FROM zhcxkj_datacenter.other_order  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and WarehouseType not in (3,81)
            """
            actual_other_order_data_res = mysql_db.fetch_all_tuple(sql, ())
            actual_other_order_data_set = set(actual_other_order_data_res)
            other_order_data_set = set(other_order_data_res)
            if other_order_data_set == actual_other_order_data_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" other_order 表缺失{len(other_order_data_set - actual_other_order_data_set)}  个 \n"
                                   f"缺失{other_order_data_set - actual_other_order_data_set}   \n"
                                   f"多了{actual_other_order_data_set - other_order_data_set}")

        if procurement_putin_data_res:
            sql = f"""               
            SELECT BillNumber,VerificationOrderNo,SKU,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.procurement_putin  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            # and WarehouseType not in (3,81)
            """
            actual_procurement_putin_data_res = mysql_db.fetch_all_tuple(sql, ())
            actual_procurement_putin_data_set = set(actual_procurement_putin_data_res)
            expect_procurement_putin_data_set = set(procurement_putin_data_res)
            if expect_procurement_putin_data_set == actual_procurement_putin_data_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" procurement_putin 表缺失{len(expect_procurement_putin_data_set - actual_procurement_putin_data_set)}  个 \n"
                                   f"缺失{expect_procurement_putin_data_set - actual_procurement_putin_data_set}   \n"
                                   f"多了{actual_procurement_putin_data_set - expect_procurement_putin_data_set}")

        if sales_putout_data_res:
            sql = f"""               
            SELECT BillNumber,ClientOrderNo,SKU ,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.sales_putout  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and WarehouseType not in (3,81)
            """
            actual_sales_putout_data_res = mysql_db.fetch_all_tuple(sql, ())
            actual_sales_putout_data_set = set(actual_sales_putout_data_res)
            expect_sales_putout_data_set = set(sales_putout_data_res)
            if expect_sales_putout_data_set == actual_sales_putout_data_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" sales_putout 表缺失{len(expect_sales_putout_data_set - actual_sales_putout_data_set)}  个 \n"
                                   f"缺失{expect_sales_putout_data_set - actual_sales_putout_data_set}   \n"
                                   f"多了{actual_sales_putout_data_set - expect_sales_putout_data_set}")

        if supplier_returns_order_res:
            sql = f"""               
            SELECT BillNumber,OrderNo,SKU,ABS(ProductQuantity)
            FROM zhcxkj_datacenter.supplier_returns_order  
            WHERE AddTime >='{AddTime_start}' AND AddTime<= '{AddTime_end}'
            and WarehouseType not in (3,81)
            """
            actual_supplier_returns_order_res = mysql_db.fetch_all_tuple(sql, ())
            actual_supplier_returns_order_set = set(actual_supplier_returns_order_res)
            expect_supplier_returns_order_set = set(supplier_returns_order_res)
            if expect_supplier_returns_order_set == actual_supplier_returns_order_set:
                pass
            else:
                ERROR.logger.error(f"AddTime_start={AddTime_start}——AddTime_end={AddTime_end}"
                                   f" supplier_returns_order 表缺失{len(expect_supplier_returns_order_set - actual_supplier_returns_order_set)}个 \n"
                                   f"缺失{expect_supplier_returns_order_set - actual_supplier_returns_order_set}   \n"
                                   f"多了{actual_supplier_returns_order_set - expect_supplier_returns_order_set}")

    def handler_data_no_allocation_bill(self, res):

        # allocation_bill_out_data = list(filter(lambda x: x[7] in [5], res))
        # allocation_bill_in_data = list(filter(lambda x: x[7] in [4], res))
        # allocation_bill_out_data = list(map(lambda x: (x[0], x[1], x[3]), allocation_bill_out_data))
        # allocation_bill_in_data = list(map(lambda x: (x[0], x[1], x[3]), allocation_bill_in_data))
        inventory_check_order_data = list(filter(lambda x: x[7] in [6, 7], res))
        inventory_check_order_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), inventory_check_order_data))
        other_order_data = list(filter(lambda x: x[7] in [1, 3], res))
        other_order_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), other_order_data))
        procurement_putin_data = list(filter(lambda x: x[7] == 0 and x[2] != 1530, res))
        procurement_putin_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), procurement_putin_data))
        sales_putout_data = list(filter(lambda x: x[7] == 2, res))
        sales_putout_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), sales_putout_data))
        supplier_returns_order = list(filter(lambda x: x[7] == 8, res))
        supplier_returns_order = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), supplier_returns_order))
        return inventory_check_order_data, other_order_data, procurement_putin_data, sales_putout_data, supplier_returns_order

    def handler_data(self, res):

        allocation_bill_out_data = list(filter(lambda x: x[7] in [5], res))
        allocation_bill_in_data = list(filter(lambda x: x[7] in [4], res))
        allocation_bill_out_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), allocation_bill_out_data))
        allocation_bill_in_data = list(map(lambda x: (x[0], x[1], x[3], abs(x[8])), allocation_bill_in_data))
        return allocation_bill_out_data, allocation_bill_in_data

    def insert_allocation_bill_data(self, insert_db, allocation_bill_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.allocation_bill (BillNumber,AllocationOrderNo,ToWarehouseId,SKU) VALUES (%s,%s,%s,%s);
        """
        allocation_bill_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), allocation_bill_data))
        rows = insert_db.execute_many(insert_sql, allocation_bill_data)
        if rows == 0:
            ERROR.logger.error(f"""INSERT IGNORE INTO zhcxkj_datacenter.allocation_bill 
            (BillNumber,AllocationOrderNo,ToWarehouseId,SKU) VALUES (%s,%s,%s,%s);插入失败
            data = {allocation_bill_data}
                """)

    def insert_inventory_check_order_data(self, insert_db, inventory_check_order_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.inventory_check_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
        """
        inventory_check_order_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), inventory_check_order_data))
        rows = insert_db.execute_many(insert_sql, inventory_check_order_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.inventory_check_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {inventory_check_order_data}
                """)

    def insert_other_order_data(self, insert_db, other_order_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.other_order (CustomerId,BillNumber,OrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        other_order_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), other_order_data))
        rows = insert_db.execute_many(insert_sql, other_order_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.other_order (CustomerId,BillNumber,OrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {other_order_data}
                """)

    def insert_procurement_putin_data(self, insert_db, procurement_putin_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.procurement_putin (CustomerId,BillNumber,VerificationOrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        procurement_putin_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), procurement_putin_data))
        rows = insert_db.execute_many(insert_sql, procurement_putin_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.procurement_putin (CustomerId,BillNumber,VerificationOrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {procurement_putin_data}
                """)

    def insert_sales_putout_data(self, insert_db, sales_putout_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.sales_putout (CustomerId,BillNumber,ClientOrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        sales_putout_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), sales_putout_data))
        rows = insert_db.execute_many(insert_sql, sales_putout_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.sales_putout (CustomerId,BillNumber,ClientOrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {sales_putout_data}
                """)

    def insert_supplier_returns_order(self, insert_db, supplier_returns_order):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.supplier_returns_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        supplier_returns_order = list(map(lambda x: (x[0], x[1], x[2], x[3]), supplier_returns_order))
        rows = insert_db.execute_many(insert_sql, supplier_returns_order)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.supplier_returns_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {supplier_returns_order}
                """)


if __name__ == '__main__':
    # TestChange().test_allocation_bill()
    t = TestChange()
    # t.test_allocation_bill('2023-08-01 00:00:00', '2023-08-01 23:59:59')
    def generate(month, month_days):
        res_list = []
        for i in range(1, 10):
            aa = f"{month[0:4]}-{month[4:6]}-0{i} 00:00:00"
            bb = f"{month[0:4]}-{month[4:6]}-0{i} 23:59:59"
            res_list.append((aa, bb))
        for i in range(10, month_days + 1):
            cc = f"{month[0:4]}-{month[4:6]}-{i} 00:00:00"
            dd = f"{month[0:4]}-{month[4:6]}-{i} 23:59:59"
            res_list.append((cc, dd))
        return res_list


    time_list = generate("202308",31)
    executor = ThreadPoolExecutor(max_workers=8)
    tasks = []
    for i in range(len(time_list)):
        # 使用funools.partial函数创建一个新的函数，将参数绑定到函数中
        new_task = partial(t.test_allocation_bill, time_list[i][0], time_list[i][1])
        # executor.submit(new_task)
        tasks.append(executor.submit(new_task))

    # 处理已完成的任务
    for future in as_completed(tasks):
        try:
            result = future.result()
            # 处理任务执行的结果
            print(result)
        except Exception as e:
            # 处理任务执行出的异常
            print(f"任务{i}执行出现异常，参数为{new_task.args}")
            raise

    # 关闭线程池
    executor.shutdown()
